import smap.archiver.client
import csv
import calendar
from datetime import datetime
from dateutil import tz

#change the name and location of the output file
f1 = open('/home/cbe/.gvfs/bsg on 169.229.130.74/SIMULATIONS/PROJECTS/NYT_CPB_Calib model/Analysis/DH/cart_test1.csv', 'wb')
testsout = csv.writer(f1,dialect='excel') 
testsout.writerow(['Test Grid',-5,4,10,24,48,67,104])

#connect to our smap db
c = smap.archiver.client.SmapClient(base='http://new.openbms.org/backend', key='SA2nYWuHrJxmPNK96pdLKhnSSYQSPdALkvnA', private=True)

testInfo = []
testInfo.append(['AA12','Q3','O6','A10','H23','O37','AD10','X12','AC27','AD29','Y30','T36','O33','K36','A29','A17','E31','A10','G10','J2','P6','Z15','AA24','T3','AB11','AE11','W10','AA18','Y18','AE22','Z25','AA28','AE28','X30','AB33','T37','J37','O36','A29','C24','F21','A17','E13','A9','G9','J2','K6','P2','T3','Y6','T1','S7','L7','J2','F9','C12','A16','F18','B24','A28','E29','K38','U37','T33','K32','AE33','AB28','AD23','AA14'])
testInfo.append(['2011-08-25 10:33:59','2011-08-25 10:44:08','2011-08-25 11:05:43','2011-08-25 11:16:39','2011-08-25 11:42:16','2011-08-25 11:53:55','2011-08-26 09:47:20','2011-08-26 09:55:48','2011-08-26 10:05:03','2011-08-26 10:14:13','2011-08-26 10:24:04','2011-08-26 10:31:13','2011-08-26 11:01:56','2011-08-26 11:09:26','2011-08-26 13:31:55','2011-08-26 13:39:42','2011-08-26 13:49:05','2011-08-26 13:57:07','2011-08-26 14:04:26','2011-08-26 14:13:48','2011-08-26 14:24:26','2011-08-26 14:35:25','2011-08-26 14:48:54','2011-08-26 14:56:31','2012-01-09 10:28:37','2012-01-09 10:36:48','2012-01-09 10:46:04','2012-01-09 10:58:04','2012-01-09 11:05:24','2012-01-09 11:18:14','2012-01-09 11:27:33','2012-01-09 11:35:21','2012-01-09 11:47:34','2012-01-09 11:56:21','2012-01-09 12:06:42','2012-01-09 12:18:01','2012-01-09 12:40:00','2012-01-09 13:04:01','2012-01-09 14:20:08','2012-01-09 14:29:28','2012-01-09 14:45:46','2012-01-09 15:04:23','2012-01-09 15:15:21','2012-01-09 15:26:40','2012-01-09 15:40:32','2012-01-09 15:56:57','2012-01-09 16:13:07','2012-01-09 16:29:17','2012-01-09 16:41:26','2012-01-09 16:56:12','2012-01-10 09:35:07','2012-01-10 09:45:55','2012-01-10 09:55:07','2012-01-10 10:09:48','2012-01-10 10:23:58','2012-01-10 10:50:30','2012-01-10 11:07:35','2012-01-10 11:21:32','2012-01-10 11:41:21','2012-01-10 12:05:49','2012-01-10 12:30:50','2012-01-10 12:55:48','2012-01-10 13:52:13','2012-01-10 14:01:08','2012-01-10 14:12:49','2012-01-10 14:27:55','2012-01-10 14:40:46','2012-01-10 15:06:59','2012-01-10 19:02:03'])
testInfo.append(['2011-08-25 10:35:59','2011-08-25 10:46:08','2011-08-25 11:07:43','2011-08-25 11:18:39','2011-08-25 11:44:16','2011-08-25 11:55:55','2011-08-26 09:49:20','2011-08-26 09:57:48','2011-08-26 10:07:03','2011-08-26 10:16:13','2011-08-26 10:26:04','2011-08-26 10:33:13','2011-08-26 11:03:56','2011-08-26 11:11:26','2011-08-26 13:33:55','2011-08-26 13:41:42','2011-08-26 13:51:05','2011-08-26 13:59:07','2011-08-26 14:06:26','2011-08-26 14:15:48','2011-08-26 14:26:26','2011-08-26 14:37:25','2011-08-26 14:50:54','2011-08-26 14:58:31','2012-01-09 10:30:37','2012-01-09 10:38:48','2012-01-09 10:48:04','2012-01-09 11:00:04','2012-01-09 11:07:24','2012-01-09 11:20:14','2012-01-09 11:29:33','2012-01-09 11:37:21','2012-01-09 11:49:34','2012-01-09 11:58:21','2012-01-09 12:08:42','2012-01-09 12:20:01','2012-01-09 12:42:00','2012-01-09 13:06:01','2012-01-09 14:22:08','2012-01-09 14:31:28','2012-01-09 14:47:46','2012-01-09 15:06:23','2012-01-09 15:17:21','2012-01-09 15:28:40','2012-01-09 15:42:32','2012-01-09 15:58:57','2012-01-09 16:15:07','2012-01-09 16:31:17','2012-01-09 16:43:26','2012-01-09 16:58:12','2012-01-10 09:37:07','2012-01-10 09:47:55','2012-01-10 09:57:07','2012-01-10 10:11:48','2012-01-10 10:25:58','2012-01-10 10:52:30','2012-01-10 11:09:35','2012-01-10 11:23:32','2012-01-10 11:43:21','2012-01-10 12:07:49','2012-01-10 12:32:50','2012-01-10 12:57:48','2012-01-10 13:54:13','2012-01-10 14:03:08','2012-01-10 14:14:49','2012-01-10 14:29:55','2012-01-10 14:42:46','2012-01-10 15:08:59','2012-01-10 19:04:03'])

for y in xrange(len(testInfo[0])):
    #print testInfo[1][y]
    #insert time zone and local time (local to project time zone)
    #takes a time, adds project time zone to it, converts to utc time tuple and then to utc seconds since epoch for smap
    dt_format = '%Y-%m-%d %H:%M:%S'
    time_zone = tz.gettz('EST5EDT')
    start_time = int(calendar.timegm(datetime.strptime(testInfo[1][y], dt_format).replace(tzinfo=time_zone).utctimetuple()))
    end_time = int(calendar.timegm(datetime.strptime(testInfo[2][y], dt_format).replace(tzinfo=time_zone).utctimetuple()))
    
    newdata = c.data('Metadata/Extra/DeviceName = \'Cart\'',start_time, end_time)
    values = {}
    for x in xrange(len(newdata[0])):
        uuid = newdata[0][x]
        name = repr(c.query('select distinct Metadata/Extra/SensorLocation where uuid = \'%s\'' % uuid)).strip('[u\'\']')
        if len(newdata[1][x][:,1]) > 0: #check that there is data
            value = sum(newdata[1][x][:,1])/len(newdata[1][x][:,1])
            values['%s' % name] = value
    #print values
    headings = ['Underfloor','4"','10"','24"','48"','67"','4" from ceiling']
    row = []
    row.append(testInfo[1][y])
    print testInfo[1][y]
    for heading in headings:
        if heading in values:
            row.append(values[heading])
        else:
            row.append('NaN')
    testsout.writerow(row)
f1.close()


